package com.jiuzhou.surery.db;

import java.sql.PreparedStatement;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.jiuzhou.surery.beans.Item;

import jakarta.annotation.Resource;

@Repository
public class ItemRepository {
    
    @Resource
    private final JdbcTemplate jdbcTemplate;

    public ItemRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

     public List<Item> findAll() {
        return jdbcTemplate.query("SELECT * FROM items", new ItemRowMapper());
    }


    public Item save(Item item){
        String sql = "INSERT INTO items (name, age, sex, phone, email, address, cloud_server, city, work_Years,salary,remark,photo) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(conn->{
            PreparedStatement ps = conn.prepareStatement(sql, new String[]{"id"});
            ps.setString(1, item.getName());
            ps.setInt(2, item.getAge());
            ps.setString(3, item.getSex());
            ps.setString(4, item.getPhone());
            ps.setString(5, item.getEmail());
            ps.setString(6, item.getAddress());
            ps.setString(7, String.join(",", item.getCloudserver()));
            ps.setString(8, item.getCity());
            ps.setInt(9, item.getWorkYear());
            ps.setDouble(10, item.getSalary());
            ps.setString(11, item.getRemark());
            ps.setString(12, item.getPhoto());
            return ps;
        }, keyHolder);
        Number key = keyHolder.getKey();
        int id=key!=null?key.intValue():0;
        item.setId(id);
        return new Item(id, item.getName(), item.getAge(), item.getSex(), item.getPhone(), item.getEmail(), item.getAddress(), item.getCloudserver(), item.getCity(), item.getWorkYear(), item.getSalary(), item.getRemark(), item.getPhoto());
    }


    public void deleteById(long id) {
        String sql = "DELETE FROM items WHERE id = ?";
        jdbcTemplate.update(sql, id);
    }
}
